package com.neusoft.hifly.commons.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.neusoft.hifly.commons.exception.SystemErrorException;
import com.neusoft.hifly.commons.lang.LocalDateUtil;
import com.neusoft.hifly.commons.lang.ReflectionUtil;

/**
 * Title: 核心类库
 * <p>
 * Description: Excel导入
 * <p>
 * Copyright: 2019
 * <p>
 * Company: 东软汉枫
 *
 * @author lh_neu
 * @version 1.0.0
 */
public class ExcelUtil {

	private final static String excel2003L = ".xls"; //2003- 版本的excel
	private final static String excel2007U = ".xlsx"; //2007+ 版本的excel

	/**
	 * 获取IO流中的数据
	 *
	 * @param in
	 *            流程
	 * @param fileName
	 *            文件
	 * @return 对象集合
	 * @throws Exception
	 *             Exception
	 */
	public List<List<Object>> getBankListByExcel(final InputStream in, final String fileName) throws Exception {
		List<List<Object>> list = null;

		//创建Excel工作薄
		final Workbook work = ExcelUtil.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		list = new ArrayList<List<Object>>();
		//遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}

			//遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {
				row = sheet.getRow(j);
				if (row == null || row.getFirstCellNum() == j) {
					continue;
				}

				//遍历所有的列
				final List<Object> li = new ArrayList<Object>();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					li.add(ExcelUtil.getCellValue(cell));
				}
				list.add(li);
			}
		}
		work.close();
		return list;
	}

	/**
	 * 获取Excel的列
	 *
	 * @param request
	 *            请求头
	 * @param columnRow
	 *            列头所在行
	 * @return 列头
	 */
	public static List<String> getExcelColumn(final MultipartFile file, final int columnRow)
			throws SystemErrorException {
		try {
			if (file.isEmpty()) {
				throw new SystemErrorException("未获取到文件，请重试!");
			}
			final List<String> list = new ArrayList<String>();
			final InputStream in = file.getInputStream();
			final String originalFilename = file.getOriginalFilename();
			//创建Excel工作薄
			final Workbook work = ExcelUtil.getWorkbook(in, originalFilename);
			if (null == work) {
				throw new SystemErrorException("没有获取到工作薄对象!");
			}
			Sheet sheet = null;
			Row row = null;
			Cell cell = null;
			sheet = work.getSheetAt(0);
			if (sheet == null) {
				return null;
			}
			row = sheet.getRow(columnRow);
			//没有数据或者是第一行 那么不导入
			if (row == null) {
				return null;
			}

			//获取总列数
			final int columnNum = row.getPhysicalNumberOfCells();
			for (int i = 0; i < columnNum; i++) {
				cell = row.getCell(i);
				final String value = (String) ExcelUtil.getCellValue(cell);
				list.add(value);
			}

			return list;
		} catch (final Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 导入数据获取集合
	 *
	 * @param filePath
	 *            附件地址
	 * @param clazz
	 *            对象class
	 * @param fields
	 *            字段名称集合
	 * @param fieldTypes
	 *            字段类型集合
	 * @param startRow
	 *            开始读取行
	 * @return
	 */
	public static <T> List<T> getListByExcel(final String filePath, final Class<T> clazz, final String[] fields,
			final String[] fieldTypes, final Integer[] fieldNums, final int startRow) throws SystemErrorException {
		try {

			final File file = new File(filePath);
			if (!file.exists()) {
				throw new SystemErrorException("附件不存在!");
			}

			final FileInputStream in = new FileInputStream(file);

			return ExcelUtil.read(file.getName(), clazz, fields, fieldTypes, fieldNums, startRow, in);
		} catch (final Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 导入数据获取集合
	 *
	 * @param file
	 *            附件
	 * @param clazz
	 *            对象class
	 * @param fields
	 *            字段名称集合
	 * @param fieldTypes
	 *            字段类型集合
	 * @param startRow
	 *            开始读取行
	 * @return
	 */
	public static <T> List<T> getListByExcel(final MultipartFile file, final Class<T> clazz, final String[] fields,
			final String[] fieldTypes, final Integer[] fieldNums, final int startRow) throws SystemErrorException {
		try {
			if (file.isEmpty()) {
				throw new SystemErrorException("未获取到文件，请重试!");
			}
			final InputStream in = file.getInputStream();
			return ExcelUtil.read(file.getOriginalFilename(), clazz, fields, fieldTypes, fieldNums, startRow, in);
		} catch (final Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 读取Excel文件
	 *
	 * @param fileName
	 *            文件
	 * @param clazz
	 *            对象class
	 * @param fields
	 *            字段名称集合
	 * @param fieldTypes
	 *            字段类型集合
	 * @param startRow
	 *            开始读取行
	 * @param in
	 *            流
	 * @return 对象集合
	 * @throws Exception
	 *             Exception
	 * @throws SystemErrorException
	 *             Exception
	 * @throws InstantiationException
	 *             Exception
	 * @throws IllegalAccessException
	 *             Exception
	 */
	private static <T> List<T> read(final String fileName, final Class<T> clazz, final String[] fields,
			final String[] fieldTypes, final Integer[] fieldNums, final int startRow, final InputStream in)
			throws Exception, SystemErrorException, InstantiationException, IllegalAccessException {
		final Workbook work = ExcelUtil.getWorkbook(in, fileName);
		if (null == work) {
			throw new SystemErrorException("没有获取到工作薄对象!");
		}
		final List<T> list = new ArrayList<>();
		T t;
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		//遍历Excel中所有的sheet
		final int numberOfSheets = work.getNumberOfSheets();
		for (int i = 0; i < numberOfSheets; i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			//遍历当前sheet中的所有行
			final int firstRowNum = sheet.getFirstRowNum();
			final int lastRowNum = sheet.getLastRowNum();
			for (int j = firstRowNum; j <= lastRowNum; j++) {
				//设置属相
				t = clazz.newInstance();
				row = sheet.getRow(j);
				//没有数据或者是第一行 那么不导入
				if (row == null || j < startRow) {
					continue;
				}

				final int length = fieldNums.length;
				int failCount = 0;
				for (int m = 0; m < length; m++) {
					cell = row.getCell(fieldNums[m] - 1);
					if (cell == null) {
						failCount++;
					}
					final Object value = ExcelUtil.getCellValue(cell);
					final String s = value.toString();
					if (value == null || StringUtils.isBlank(s)) {
						failCount++;
					}

					final String methodName = "set" + fields[m].substring(0, 1).toUpperCase() + fields[m].substring(1);

					ReflectionUtil.setMethod(t, methodName, fieldTypes[m], value);
				}
				if (failCount != length) {
					list.add(t);
				}
			}
		}
		return list;
	}

	/**
	 * 根据文件后缀，自适应上传文件的版本
	 *
	 * @param inStr
	 *            流
	 * @param fileName
	 *            文件名
	 * @return 工作簿
	 * @throws Exception
	 *             Exception
	 */
	private static Workbook getWorkbook(final InputStream inStr, final String fileName) throws Exception {
		Workbook wb = null;
		final String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (ExcelUtil.excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); //2003-
		} else if (ExcelUtil.excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); //2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 对表格中数值进行格式化
	 *
	 * @param cell
	 *            单元格
	 * @return 读取的内容
	 */
	@SuppressWarnings("deprecation")
	private static Object getCellValue(final Cell cell) {
		Object value = null;
		final DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
		final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); //日期格式化
		final DecimalFormat df2 = new DecimalFormat("#"); //格式化数字
		switch (cell.getCellTypeEnum()) {
			case STRING:
				value = cell.getRichStringCellValue().getString().replaceAll("\r|\n", "").trim();
				break;
			case NUMERIC:
				if ("General".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("yyyy-mm-dd".equals(cell.getCellStyle().getDataFormatString())) {
					value = LocalDateUtil.strToDate(sdf.format(cell.getDateCellValue()));
				} else if ("yyyy/mm/dd".equals(cell.getCellStyle().getDataFormatString())) {
					value = LocalDateUtil.strToDate(sdf.format(cell.getDateCellValue()));
				} else if ("yyyy\\-mm\\-dd".equals(cell.getCellStyle().getDataFormatString())) {
					value = LocalDateUtil.strToDate(sdf.format(cell.getDateCellValue()));
				} else if (String.valueOf(cell.getNumericCellValue()).contains(".")) {
					value = Float.parseFloat(df2.format(cell.getNumericCellValue()));
				} else {
					value = Integer.parseInt(df2.format(cell.getNumericCellValue()));
				}
				break;
			case BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case BLANK:
				value = "";
				break;
			default:
				break;
		}
		return value;
	}

}